package br.ufrn.opencopi.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import br.cnpq.webflowah.beans.AbstractQualityParameter;
import br.cnpq.webflowah.beans.Metadata;
import br.cnpq.webflowah.beans.ParameterBoundary;
import br.cnpq.webflowah.beans.QoC;
import br.cnpq.webflowah.beans.QoS;
import br.ufrn.opencopi.exceptions.ParameterNotExistException;

public class QualityParameterDB {
	private static Map<String, Metadata> metadataLocator = new HashMap<String, Metadata>();
	private static Map<String, AbstractQualityParameter> absParameterLocator = new Hashtable<String, AbstractQualityParameter>();
	private static Connection connection = null;
	private static final Logger log = Logger
			.getLogger(QualityParameterDB.class);

	private static Connection getConnection() {
		try {
			if (connection != null && !connection.isClosed())
				return connection;
			else
				Class.forName("org.h2.Driver");
			connection = DriverManager
					.getConnection("jdbc:h2:~/test", "sa", "");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return connection;
	}

	public static void insertQualityParameter(
			AbstractQualityParameter qualityParameter) {
		AbstractQualityParameter parameter = QualityParameterDB.getParameter(
				qualityParameter.getName(), qualityParameter.getServiceURI());
		if (parameter == null) {// parametro ainda n�o existente no bd
			Connection connection = null;
			PreparedStatement pstm = null;

			try {
				connection = getConnection();
				String sql = "insert into qualityparameter (name, serviceuri, value, impact, nature,"
						+ "lastmeasurement, isqocparameter) values (?, ?, ?, ?, ?, ?, ?)";

				pstm = connection.prepareStatement(sql);

				pstm.setString(1, qualityParameter.getName());
				pstm.setString(2, qualityParameter.getServiceURI());
				pstm.setString(3, qualityParameter.getValue());
				pstm.setString(4, qualityParameter.getImpact());
				pstm.setString(5, qualityParameter.getNature());
				pstm.setString(6, qualityParameter.getLastMeasurement());
				if (qualityParameter instanceof QoC)
					pstm.setBoolean(7, true);
				else
					pstm.setBoolean(7, false);

				pstm.execute();
				pstm.close();
			} catch (SQLException e) {
				System.err
						.println("Erro: QualityParameterDB/insertQualityParameter()");
				e.printStackTrace();
			} catch (Exception e) {
				System.err
						.println("Erro: QualityParameterDB/insertQualityParameter()");
				e.printStackTrace();
			}
		} else
			// caso j� exista, s� atualiza.
			QualityParameterDB.updateParameter(qualityParameter);
	}

	public static List<AbstractQualityParameter> listParameters()
			throws SQLException {
		Connection connection = null;
		PreparedStatement pstm = null;
		ResultSet result = null;
		List<AbstractQualityParameter> parameters = null;

		try {
			connection = getConnection();
			String sql = "select * from qualityparameter order by serviceuri,name";

			pstm = connection.prepareStatement(sql);
			result = pstm.executeQuery();

			parameters = new ArrayList<AbstractQualityParameter>();

			while (result.next()) {

				AbstractQualityParameter parameter;

				if (result.getBoolean("isqocparameter"))
					parameter = new QoC();
				else
					parameter = new QoS();

				parameter.setServiceURI(result.getString("serviceuri"));
				parameter.setName(result.getString("name"));
				parameter.setValue(result.getString("value"));
				parameter.setImpact(result.getString("impact"));
				parameter.setNature(result.getString("nature"));
				parameter.setLastMeasurement(result
						.getString("lastmeasurement"));

				parameters.add(parameter);
			}

			result.close();
			pstm.close();
			connection.close();

		} catch (SQLException e) {
			System.err.println("Erro: QualityParameterDB/listParameters()");
			e.printStackTrace();
		} catch (Exception e) {
			System.err.println("Erro: QualityParameterDB/listParameters()");
			e.printStackTrace();
		}
		log.info(parameters);

		return parameters;
	}

	public static void updateParameter(AbstractQualityParameter parameter) {
		Connection connection = null;
		PreparedStatement pstm = null;

		try {
			connection = getConnection();
			String sql = "update qualityparameter set value = ?, impact = ?, nature = ?, lastmeasurement = ?,"
					+ " isqocparameter = ? where (name = ? and serviceuri = ?) ";
			pstm = connection.prepareStatement(sql);

			pstm.setString(1, parameter.getValue());
			pstm.setString(2, parameter.getImpact());
			pstm.setString(3, parameter.getNature());
			pstm.setString(4, parameter.getLastMeasurement());
			if (parameter instanceof QoC)
				pstm.setBoolean(5, true);
			else
				pstm.setBoolean(5, false);
			pstm.setString(6, parameter.getName());
			pstm.setString(7, parameter.getServiceURI());

			pstm.execute();

			pstm.close();
			connection.close();
		} catch (SQLException e) {
			System.err.println("Erro: QualityParameterDB/updateParameters()");
			e.printStackTrace();
		} catch (Exception e) {
			System.err.println("Erro: QualityParameterDB/updateParameters()");
			e.printStackTrace();
		}

	}

	public static void removeParameter(AbstractQualityParameter parameter)
			throws SQLException {
		Connection connection = null;
		PreparedStatement pstm = null;

		try {
			connection = getConnection();
			String sql = "delete from qualityparameter where name = ? and serviceuri = ?";
			pstm = connection.prepareStatement(sql);

			pstm.setString(1, parameter.getName());
			pstm.setString(2, parameter.getServiceURI());

			pstm.execute();

			pstm.close();
			connection.close();
		} catch (SQLException e) {
			System.err.println("Erro: QualityParameterDB/removeParameter()");
			e.printStackTrace();
		} catch (Exception e) {
			System.err.println("Erro: QualityParameterDB/removeParameter()");
			e.printStackTrace();
		}
	}

	/**
	 * Deleta todos os parametros de um servi�o
	 * 
	 * @param serviceURI
	 * @throws SQLException
	 */
	public static void removeParameterByService(String serviceURI) {
		Connection connection = null;
		PreparedStatement pstm = null;

		try {
			connection = getConnection();
			String sql = "delete from qualityparameter where serviceuri = ?";
			pstm = connection.prepareStatement(sql);

			pstm.setString(1, serviceURI);

			pstm.execute();

			pstm.close();
			connection.close();
		} catch (SQLException e) {
			System.err.println("Erro: QualityParameterDB/removeParameter()");
			e.printStackTrace();
		} catch (Exception e) {
			System.err.println("Erro: QualityParameterDB/removeParameter()");
			e.printStackTrace();
		}
	}

	public static AbstractQualityParameter getParameter(String parameterName,
			String serviceURI) {
		AbstractQualityParameter absParameter = absParameterLocator
				.get(parameterName + serviceURI);
		if (absParameter != null) {
			log.debug("Return AbstractQualityParameter " + serviceURI
					+ " from Locator");
			return absParameter;
		}
		AbstractQualityParameter parameter = null;
		ResultSet rs = null;
		Connection conn = null;
		Statement st = null;

		try {
			conn = getConnection();
			st = conn.createStatement();

			String query = "select * from qualityparameter where name = '"
					+ parameterName + "' AND serviceuri = '" + serviceURI + "'";
			rs = st.executeQuery(query);
			if (rs.next()) {

				if (rs.getBoolean("isqocparameter"))
					parameter = new QoC();
				else
					parameter = new QoS();

				parameter.setName(rs.getString("name"));
				parameter.setServiceURI(rs.getString("serviceuri"));
				parameter.setValue(rs.getString("value"));
				parameter.setImpact(rs.getString("impact"));
				parameter.setNature(rs.getString("nature"));
				parameter.setLastMeasurement(rs.getString("lastmeasurement"));

			} else {
				throw new ParameterNotExistException(
						"Parameter does not exist.");
			}

			rs.close();
			st.close();
			conn.close();
		} catch (ParameterNotExistException e) {
			return null;
		} catch (SQLException e) {
			System.err.println("Erro: QualityParameterDB/getParameter()");
			e.printStackTrace();
		} catch (Exception e) {
			System.err.println("Erro: QualityParameterDB/getParameter()");
			e.printStackTrace();
		}
		absParameterLocator.put(parameterName + serviceURI, parameter);
		return parameter;
	}

	public static void clearMetadataLocator() {
		if (metadataLocator != null)
			metadataLocator.clear();
	}

	public static void clearAbsQualityParameterLocator() {
		if (absParameterLocator != null)
			absParameterLocator.clear();
	}

	public static Metadata listParametersByService(String serviceURI) {
		Metadata aux = metadataLocator.get(serviceURI);
		if (aux != null) {
			log.debug("Return Metadata " + serviceURI + " from locator");
			return aux;
		}
		Connection connection = null;
		PreparedStatement pstm = null;
		Metadata metadata = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			String sql = "select * from qualityparameter where serviceuri = '"
					+ serviceURI + "'";

			pstm = connection.prepareStatement(sql);
			result = pstm.executeQuery();

			metadata = new Metadata();

			while (result.next()) {

				AbstractQualityParameter parameter;

				if (result.getBoolean("isqocparameter"))
					parameter = new QoC();
				else
					parameter = new QoS();

				parameter.setServiceURI(result.getString("serviceuri"));
				parameter.setName(result.getString("name"));
				parameter.setValue(result.getString("value"));
				parameter.setImpact(result.getString("impact"));
				parameter.setNature(result.getString("nature"));
				parameter.setLastMeasurement(result
						.getString("lastmeasurement"));

				metadata.insertQualityParameter(parameter);

			}
			result.close();
			pstm.close();
			connection.close();

		} catch (SQLException e) {
			System.err
					.println("Erro: QualityParameterDB/listParametersByService()");
			e.printStackTrace();
		} catch (Exception e) {
			System.err
					.println("Erro: QualityParameterDB/listParametersByService()");
			e.printStackTrace();
		}
		metadataLocator.put(serviceURI, metadata);
		return metadata;
	}

	public static Hashtable<String, ParameterBoundary> getBoudariesOfParameters() {
		Hashtable<String, ParameterBoundary> parametersBoundaries = new Hashtable<String, ParameterBoundary>();
		ParameterBoundary pBoundary = new ParameterBoundary();

		ResultSet rs = null;
		Connection conn = null;
		Statement st = null;
		String p = "";

		List<String> parametersName = getParametersName();

		for (int i = 0; i < parametersName.size(); i++) {
			p = parametersName.get(i);
			try {
				conn = getConnection();
				st = conn.createStatement();

				String query = "select min(value) as min FROM QUALITYPARAMETER where NAME = '"
						+ p + "'";
				rs = st.executeQuery(query);
				if (rs.next()) {
					pBoundary = new ParameterBoundary();
					pBoundary.setMinValue(rs.getDouble("min"));
					pBoundary.setParameterName(p);

				} else {
					throw new ParameterNotExistException(
							"Parameter does not exist.");
				}

			} catch (ParameterNotExistException e) {
				return null;
			} catch (SQLException e) {
				System.err
						.println("Erro: QualityParameterDB/getBoudariesOfParameters() - min value");
				e.printStackTrace();
			} catch (Exception e) {
				System.err
						.println("Erro: QualityParameterDB/getBoudariesOfParameters() - min value");
				e.printStackTrace();
			}

			// Pegando o maximo
			try {

				st = conn.createStatement();

				String query = "select max(value) as max FROM QUALITYPARAMETER where NAME = '"
						+ p + "'";
				rs = st.executeQuery(query);
				if (rs.next()) {

					pBoundary.setMaxValue(rs.getDouble("max"));

				} else {
					throw new ParameterNotExistException(
							"Parameter does not exist.");
				}

				rs.close();
				st.close();
				conn.close();
			} catch (ParameterNotExistException e) {
				return null;
			} catch (SQLException e) {
				System.err
						.println("Erro: QualityParameterDB/getBoudariesOfParameters() - max value");
				e.printStackTrace();
			} catch (Exception e) {
				System.err
						.println("Erro: QualityParameterDB/getBoudariesOfParameters() - max value");
				e.printStackTrace();
			}
			parametersBoundaries.put(p, pBoundary);
		}

		return parametersBoundaries;

	}

	private static List<String> getParametersName() {
		List<String> parametersName = new ArrayList<String>();

		Connection connection = null;
		PreparedStatement pstm = null;
		ResultSet result = null;

		try {
			connection = getConnection();
			String sql = "SELECT DISTINCT name FROM qualityparameter";

			pstm = connection.prepareStatement(sql);
			result = pstm.executeQuery();

			while (result.next()) {
				parametersName.add((result.getString("name")));
			}

			result.close();
			pstm.close();
			connection.close();

		} catch (SQLException e) {
			System.err.println("Erro: QualityParameterDB/getParametersName()");
			e.printStackTrace();
		} catch (Exception e) {
			System.err.println("Erro: QualityParameterDB/getParametersName()");
			e.printStackTrace();
		}
		return parametersName;
	}
}